﻿using BW.WMS.Application.Features.Bins.Dtos;
using BW.WMS.Application.Features.Location.Dtos;
using BW.WMS.Application.Features.Location.Interfaces;
using BW.WMS.Core.Enums;
using BW.WMS.Core.Result;
using BW.WMS.Infrastructure.Data.Contexts;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Yitter.IdGenerator;

namespace BW.WMS.Application.Features.Location.Services
{
    /// <summary>
    /// WMS仓库管理—库位服务实现层
    /// </summary>
    public class WMS_LocationService : IWMS_LocationService
    {
        /// <summary>
        /// 数据库上下文
        /// </summary>
        private readonly DBContext dbContext;
        /// <summary>
        /// 构造函数注入数据库上下文
        /// </summary>
        /// <param name="dbContext">数据库上下文</param>
        public WMS_LocationService(DBContext dbContext)
        {
            this.dbContext = dbContext;
        }
        /// <summary>
        /// 获取库位分页列表，支持多条件查询
        /// </summary>
        /// <param name="queryDto">查询条件</param>
        /// <returns>返回查询分页结果</returns>
        public async Task<ApiPage<WMS_LocationDto>> GetLocationPageListAsync(WMS_LocationQueryDto queryDto)
        {
            var apiPage = new ApiPage<WMS_LocationDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功",
                PageData = new List<WMS_LocationDto>()
            };

            try
            {
                // 计算分页偏移量
                int offset = (queryDto.PageIndex - 1) * queryDto.PageSize;

                // 构建查询条件 - 修正表别名，使用l代表Location
                var whereClause = new StringBuilder(" WHERE l.IsDeleted = 0");
                var parameters = new DynamicParameters();
                parameters.Add("@Offset", offset);
                parameters.Add("@PageSize", queryDto.PageSize);

                if (!string.IsNullOrWhiteSpace(queryDto.Keyword))
                {
                    whereClause.Append(" AND (l.LocationCode LIKE @Keyword OR l.LocationName LIKE @Keyword)");
                    parameters.Add("@Keyword", $"%{queryDto.Keyword}%");
                }

                if (queryDto.Status.HasValue)
                {
                    whereClause.Append(" AND l.Status = @Status");
                    parameters.Add("@Status", queryDto.Status);
                }

                if (queryDto.WarehouseId.HasValue)
                {
                    whereClause.Append(" AND l.WarehouseId = @WarehouseId");
                    parameters.Add("@WarehouseId", queryDto.WarehouseId);
                }

                // 计算总数
                var countSql = $@"
            SELECT COUNT(1) 
            FROM WMS_Location l 
            {whereClause}";

                // 获取分页数据
                var pageSql = $@"
            SELECT 
                l.Id, l.LocationCode, l.LocationName, l.Capacity, 
                l.ManagerId, m.ManagerName, l.SortOrder, l.Status, 
                l.Remark, l.WarehouseId, w.WarehouseName, l.CreateBy, l.CreateTime
            FROM WMS_Location l
            LEFT JOIN WMS_Manager m ON l.ManagerId = m.Id AND m.IsDeleted = 0
            LEFT JOIN WMS_Warehouse w ON l.WarehouseId = w.Id AND w.IsDeleted = 0
            {whereClause}
            ORDER BY l.SortOrder, l.CreateTime DESC
            LIMIT @Offset, @PageSize";

                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 执行查询
                var locations = await connection.QueryAsync<WMS_LocationDto>(pageSql, parameters);
                var totalCount = await connection.ExecuteScalarAsync<int>(countSql, parameters);

                // 转换枚举值为名称
                foreach (var location in locations)
                {
                    location.StatusName = Enum.GetName(typeof(LocationStatusEnum), location.Status);
                }

                // 设置返回数据
                apiPage.PageData = locations.ToList();
                apiPage.TodalCount = totalCount;
            }
            catch (Exception ex)
            {
                apiPage.Code = ApiEnums.Error;
                apiPage.Message = $"查询失败：{ex.Message}";
            }

            return apiPage;
        }
        /// <summary>
        /// 根据ID获取库位详情，包括库位基本信息和下属货位列表
        /// </summary>
        /// <param name="id">库位ID</param>
        /// <returns>库位详情DTO，包含基本信息和货位集合</returns>
        public async Task<ApiResult<WMS_LocationDto>> GetLocationByIdAsync(long id)
        {
            var result = new ApiResult<WMS_LocationDto>
            {
                Code = ApiEnums.Success,
                Message = "查询成功"
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 构建查询库位信息的SQL
                var sql = @"
                    SELECT 
                        l.Id, l.LocationCode, l.LocationName, l.Capacity, 
                        l.ManagerId, m.ManagerName, l.SortOrder, l.Status, 
                        l.Remark, l.WarehouseId, w.WarehouseName, l.CreateBy, l.CreateTime
                    FROM WMS_Location l
                    LEFT JOIN WMS_Manager m ON l.ManagerId = m.Id AND m.IsDeleted = 0
                    LEFT JOIN WMS_Warehouse w ON l.WarehouseId = w.Id AND w.IsDeleted = 0
                    WHERE l.Id = @Id AND l.IsDeleted = 0";

                // 创建查询参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行查询，获取库位信息
                var location = await connection.QueryFirstOrDefaultAsync<WMS_LocationDto>(sql, parameters);

                // 如果库位不存在，返回失败信息
                if (location == null)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "库位不存在";
                    return result;
                }

                // 处理枚举值，转换为可读的状态名称
                location.StatusName = Enum.GetName(typeof(LocationStatusEnum), location.Status);

                // 构建查询该库位下货位列表的SQL
                var binSql = @"
                    SELECT 
                        b.Id, b.BinCode, b.BinName, 
                        b.SortOrder, b.Status, b.Remark, b.LocationId, 
                        l.LocationName, b.CreateBy, b.CreateTime
                    FROM WMS_Bin b
                    LEFT JOIN WMS_Location l ON b.LocationId = l.Id AND l.IsDeleted = 0
                    WHERE b.LocationId = @LocationId AND b.IsDeleted = 0
                    ORDER BY b.SortOrder, b.CreateTime";

                // 创建查询参数
                var binParams = new DynamicParameters();
                binParams.Add("@LocationId", id);

                // 执行查询，获取货位列表
                var bins = await connection.QueryAsync<WMS_BinDto>(binSql, binParams);

                // 处理货位状态枚举值
                foreach (var bin in bins)
                {
                    bin.StatusName = Enum.GetName(typeof(BinStatusEnum), bin.Status);
                }

                // 将货位列表添加到库位对象中
                location.Bins = bins.ToList();
                result.Result = location;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"查询失败：{ex.Message}";
            }

            return result;
        }
        /// <summary>
        /// 创建新库位
        /// </summary>
        /// <param name="createDto">库位创建DTO</param>
        /// <returns>操作结果，成功时返回新创建的库位ID</returns>
        public async Task<ApiResult<long>> CreateLocationAsync(WMS_LocationCreateDto createDto)
        {
            var result = new ApiResult<long>
            {
                Code = ApiEnums.Success,
                Message = "创建成功"
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查库位编号是否已存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Location WHERE LocationCode = @LocationCode AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@LocationCode", createDto.LocationCode);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "库位编号已存在";
                    result.Result = 0;
                    return result;
                }

                // 检查仓库是否存在
                var warehouseExistsSql = "SELECT COUNT(1) FROM WMS_Warehouse WHERE Id = @WarehouseId AND IsDeleted = 0";
                var warehouseParams = new DynamicParameters();
                warehouseParams.Add("@WarehouseId", createDto.WarehouseId);

                var warehouseExists = await connection.ExecuteScalarAsync<int>(warehouseExistsSql, warehouseParams);

                if (warehouseExists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "所选仓库不存在";
                    result.Result = 0;
                    return result;
                }

                // 检查主管ID是否存在
                var managerExistsSql = "SELECT COUNT(1) FROM WMS_Manager WHERE Id = @ManagerId AND IsDeleted = 0";
                var managerParams = new DynamicParameters();
                managerParams.Add("@ManagerId", createDto.ManagerId);

                var managerExists = await connection.ExecuteScalarAsync<int>(managerExistsSql, managerParams);

                if (managerExists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "所选主管不存在，请选择有效的主管";
                    result.Result = 0;
                    return result;
                }

                // 生成雪花ID
                long id = YitIdHelper.NextId();

                // 构建插入库位数据的SQL，使用雪花ID
                var sql = @"
            INSERT INTO WMS_Location (
                Id, LocationCode, LocationName, Capacity, ManagerId, 
                SortOrder, Status, Remark, WarehouseId, 
                CreateBy, CreateTime, UpdateBy, UpdateTime, IsDeleted
            ) VALUES (
                @Id, @LocationCode, @LocationName, @Capacity, @ManagerId, 
                @SortOrder, @Status, @Remark, @WarehouseId, 
                @CreateBy, NOW(), @CreateBy, NOW(), 0
            );";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@LocationCode", createDto.LocationCode);
                parameters.Add("@LocationName", createDto.LocationName);
                parameters.Add("@Capacity", createDto.Capacity);
                parameters.Add("@ManagerId", createDto.ManagerId);
                parameters.Add("@SortOrder", createDto.SortOrder);
                parameters.Add("@Status", createDto.Status);
                parameters.Add("@Remark", createDto.Remark);
                parameters.Add("@WarehouseId", createDto.WarehouseId);
                parameters.Add("@CreateBy", createDto.CreateBy);

                // 执行SQL并设置返回的ID
                await connection.ExecuteAsync(sql, parameters);

                // 更新仓库的库位数量
                var updateWarehouseSql = @"
            UPDATE WMS_Warehouse 
            SET LocationCount = LocationCount + 1,
                UpdateBy = @UpdateBy,
                UpdateTime = NOW()
            WHERE Id = @Id";

                var updateParams = new DynamicParameters();
                updateParams.Add("@Id", createDto.WarehouseId);
                updateParams.Add("@UpdateBy", createDto.CreateBy);

                await connection.ExecuteAsync(updateWarehouseSql, updateParams);

                result.Result = id;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"创建失败：{ex.Message}";
                result.Result = 0;
            }

            return result;
        }
        /// <summary>
        /// 删除库位（逻辑删除）
        /// </summary>
        /// <param name="id">库位ID</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> DeleteLocationAsync(long id)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 获取库位信息（包括所属仓库ID）
                var locationSql = "SELECT WarehouseId FROM WMS_Location WHERE Id = @Id AND IsDeleted = 0";
                var locationParams = new DynamicParameters();
                locationParams.Add("@Id", id);

                var warehouseId = await connection.ExecuteScalarAsync<long?>(locationSql, locationParams);

                if (!warehouseId.HasValue)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "库位不存在";
                    result.Result = false;
                    return result;
                }

                // 检查是否有货位关联到该库位
                var checkBinSql = "SELECT COUNT(1) FROM WMS_Bin WHERE LocationId = @Id AND IsDeleted = 0";
                var checkParams = new DynamicParameters();
                checkParams.Add("@Id", id);

                var hasBins = await connection.ExecuteScalarAsync<int>(checkBinSql, checkParams);

                if (hasBins > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "该库位下存在货位，不能删除";
                    result.Result = false;
                    return result;
                }

                // 构建逻辑删除库位的SQL
                var sql = @"
            UPDATE WMS_Location SET
                IsDeleted = 1,
                UpdateTime = NOW()
            WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);

                // 执行删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);

                if (affected > 0)
                {
                    // 更新仓库的库位数量
                    var updateWarehouseSql = @"
                UPDATE WMS_Warehouse 
                SET LocationCount = LocationCount - 1,
                    UpdateTime = NOW()
                WHERE Id = @Id AND LocationCount > 0";

                    var updateParams = new DynamicParameters();
                    updateParams.Add("@Id", warehouseId.Value);

                    await connection.ExecuteAsync(updateWarehouseSql, updateParams);
                }

                result.Result = affected > 0;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }

        /// <summary>
        /// 批量删除库位（逻辑删除）
        /// </summary>
        /// <param name="ids">库位ID列表</param>
        /// <returns>操作结果，表示删除是否成功</returns>
        public async Task<ApiResult<bool>> BatchDeleteLocationsAsync(List<long> ids)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "批量删除成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查是否有货位关联到这些库位
                var checkBinSql = "SELECT COUNT(1) FROM WMS_Bin WHERE LocationId IN @Ids AND IsDeleted = 0";
                var checkParams = new DynamicParameters();
                checkParams.Add("@Ids", ids);

                var hasBins = await connection.ExecuteScalarAsync<int>(checkBinSql, checkParams);

                if (hasBins > 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "选中的库位中存在关联的货位，不能删除";
                    result.Result = false;
                    return result;
                }

                // 获取这些库位所属的仓库ID
                var warehouseSql = @"
            SELECT WarehouseId, COUNT(1) as LocationCount 
            FROM WMS_Location 
            WHERE Id IN @Ids AND IsDeleted = 0 
            GROUP BY WarehouseId";

                var warehouseParams = new DynamicParameters();
                warehouseParams.Add("@Ids", ids);

                var warehouseCounts = await connection.QueryAsync<dynamic>(warehouseSql, warehouseParams);

                // 构建批量逻辑删除库位的SQL
                var sql = @"
            UPDATE WMS_Location SET
                IsDeleted = 1,
                UpdateTime = NOW()
            WHERE Id IN @Ids";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Ids", ids);

                // 执行批量删除操作
                var affected = await connection.ExecuteAsync(sql, parameters);

                // 更新各仓库的库位数量
                foreach (var item in warehouseCounts)
                {
                    var updateWarehouseSql = @"
                UPDATE WMS_Warehouse 
                SET LocationCount = LocationCount - @LocationCount,
                    UpdateTime = NOW()
                WHERE Id = @Id AND LocationCount >= @LocationCount";

                    var updateParams = new DynamicParameters();
                    updateParams.Add("@Id", (long)item.WarehouseId);
                    updateParams.Add("@LocationCount", (int)item.LocationCount);

                    await connection.ExecuteAsync(updateWarehouseSql, updateParams);
                }

                result.Result = affected > 0;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"批量删除失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }
        /// <summary>
        /// 更新库位信息
        /// </summary>
        /// <param name="updateDto">库位更新DTO</param>
        /// <returns>操作结果，表示更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateLocationAsync(WMS_LocationUpdateDto updateDto)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查库位是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Location WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", updateDto.Id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "库位不存在";
                    result.Result = false;
                    return result;
                }

                // 构建更新库位数据的SQL
                var sql = @"
                    UPDATE WMS_Location SET
                        LocationName = @LocationName,
                        Capacity = @Capacity,
                        ManagerId = @ManagerId,
                        SortOrder = @SortOrder,
                        Status = @Status,
                        Remark = @Remark,
                        UpdateBy = @UpdateBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", updateDto.Id);
                parameters.Add("@LocationName", updateDto.LocationName);
                parameters.Add("@Capacity", updateDto.Capacity);
                parameters.Add("@ManagerId", updateDto.ManagerId);
                parameters.Add("@SortOrder", updateDto.SortOrder);
                parameters.Add("@Status", updateDto.Status);
                parameters.Add("@Remark", updateDto.Remark);
                parameters.Add("@UpdateBy", updateDto.UpdateBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
        }
        /// <summary>
        /// 更新库位状态
        /// </summary>
        /// <param name="id">库位ID</param>
        /// <param name="status">新状态值</param>
        /// <param name="updatedBy">操作人</param>
        /// <returns>操作结果，表示状态更新是否成功</returns>
        public async Task<ApiResult<bool>> UpdateLocationStatusAsync(long id, int status, string updatedBy)
        {
            var result = new ApiResult<bool>
            {
                Code = ApiEnums.Success,
                Message = "状态更新成功",
                Result = true
            };

            try
            {
                // 获取数据库连接
                var connection = dbContext.GetConn();

                // 检查库位是否存在
                var existsSql = "SELECT COUNT(1) FROM WMS_Location WHERE Id = @Id AND IsDeleted = 0";
                var existsParams = new DynamicParameters();
                existsParams.Add("@Id", id);

                var exists = await connection.ExecuteScalarAsync<int>(existsSql, existsParams);

                if (exists == 0)
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "库位不存在";
                    result.Result = false;
                    return result;
                }

                // 检查状态值是否在枚举范围内
                if (!Enum.IsDefined(typeof(LocationStatusEnum), status))
                {
                    result.Code = ApiEnums.Fail;
                    result.Message = "无效的状态值";
                    result.Result = false;
                    return result;
                }

                // 构建更新库位状态的SQL
                var sql = @"
                    UPDATE WMS_Location SET
                        Status = @Status,
                        UpdateBy = @UpdatedBy,
                        UpdateTime = NOW()
                    WHERE Id = @Id";

                // 创建SQL参数
                var parameters = new DynamicParameters();
                parameters.Add("@Id", id);
                parameters.Add("@Status", status);
                parameters.Add("@UpdatedBy", updatedBy);

                // 执行更新操作
                var affected = await connection.ExecuteAsync(sql, parameters);
                result.Result = affected > 0;
            }
            catch (Exception ex)
            {
                result.Code = ApiEnums.Error;
                result.Message = $"状态更新失败：{ex.Message}";
                result.Result = false;
            }

            return result;
    }
    }
}
